﻿/* Copyright (C) 2008-2009 Jorson.WHY
 * All right reserved
 * ********** Project Info ***********
 * Project Name : MyLibrary.Text
 * NameSpace : MyLibrary.Text.SQLStringBuilder
 * Filename : SQLStringBuilder
 * Create Time : 4/9/2008 9:33:59 AM
 * ********** Environment Info *******
 * Machine Name : EA-01504-11220
 * CLR Version : 2.0.50727.1433
 */

namespace MyLibrary.Text.SQLStringBuilder
{
    #region System Using
    using System;
    using System.Collections.Generic;
    using System.Text;
    #endregion

    #region 枚举类型
    /// <summary>
    /// SQL语句的类型
    /// </summary>
    public enum SQLStringType
    {
        Select = 0,
        Insert = 1,
        Update = 2,
        Delete = 3,
        Truncate = 4
    }
    /// <summary>
    /// 聚合函数
    /// </summary>
    public enum SQLFunction
    {
        None = -1,
        Avg = 0,
        Count = 1,
        CountBig = 2,
        Max = 3,
        Min = 4,
        Sum = 5,
        Distinct = 6
    }
    /// <summary>
    /// 连接类型
    /// </summary>
    public enum JoinType
    {
        InnerJoin = 0,
        LeftJoin = 1,
        RightJoin = 2
    }
    /// <summary>
    /// 运算类型
    /// </summary>
    public enum Operational
    {
        EQUAL = 0,
        NOEQUAL = 1,
        LESSTHEN = 2,
        BIGTHEN = 3,
        BETWEEN = 4,
        BIGEQUALTHEN = 5,
        LESSEQUALTHEN = 6,
        IN = 7
    }
    /// <summary>
    /// 条件连接类型
    /// </summary>
    public enum ConditionType
    {
        AND = 0,
        OR = 1,
        NONE = -1
    }
    /// <summary>
    /// 排序类型
    /// </summary>
    public enum OrderType
    {
        DESC = 0,
        ASC = 1
    }
    /// <summary>
    /// 赋值类型
    /// </summary>
    public enum ValueType
    {
        Direct = 0,
        Parameter = 1,
        Format = 2
    }
    #endregion

    /// <summary>
    /// 建立SQL语句的通用方法
    /// </summary>
    /// <remarks>
    /// 类整合了之前编写的有关编写SQL语句组合的函数
    /// </remarks>
    public class SQLStringBuilder
    {
        StringBuilder _builder = null;
        SQLStringType _type = SQLStringType.Select;

        #region 构造函数
        public SQLStringBuilder() : this(SQLStringType.Select)
        {
        }
        public SQLStringBuilder(SQLStringType type)
        {
            this._type = type;
            this._builder = new StringBuilder();
            switch (this._type)
            {
                case SQLStringType.Select:
                    _builder.Append("Select ");
                    break;
                case SQLStringType.Insert:
                    _builder.Append("Insert Into ");
                    break;
                case SQLStringType.Update:
                    _builder.Append("Update ");
                    break;
                case SQLStringType.Delete:
                    _builder.Append("Delete ");
                    break;
                case SQLStringType.Truncate:
                    _builder.Append("Truncate Table");
                    break;
                default:
                    _builder.Append("Select ");
                    break;
            }
        }
        #endregion

        public SQLStringBuilder SelectColumn(string columnName)
        {
            return this.SelectColumn(String.Empty, columnName);
        }
        public SQLStringBuilder SelectColumn(string columnName, SQLFunction function)
        {
            return SelectColumn(String.Empty, columnName, function);
        }
        public SQLStringBuilder SelectColumn(string columnName, SQLFunction function, string otherName)
        {
            return SelectColumn(String.Empty, columnName, function, otherName);
        }
        public SQLStringBuilder SelectColumn(string prefix, string columnName)
        {
            return this.SelectColumn(prefix, columnName, SQLFunction.None);
        }
        public SQLStringBuilder SelectColumn(string prefix, string columnName, SQLFunction function)
        {
            return SelectColumn(prefix, columnName, function, String.Empty);
        }
        public SQLStringBuilder SelectColumn(string prefix, string columnName, SQLFunction function, string otherName)
        {
            if (_type != SQLStringType.Select) throw new ArgumentException("设置的语句类型不正确");
            if (String.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");

            if (!String.IsNullOrEmpty(prefix)) this._builder.Append(prefix + " ");
            if (function == SQLFunction.None)
            {
                this._builder.Append(columnName + ",");
                return this;
            }

            if (function == SQLFunction.Distinct) { if (String.IsNullOrEmpty(otherName)) this._builder.Append("Distinct " + columnName + ","); else this._builder.Append("Distinct " + columnName + " As " + otherName + ","); }
            if (function == SQLFunction.Sum) { if(String.IsNullOrEmpty(otherName)) this._builder.Append("Sum(" + columnName + "),"); else this._builder.Append("Sum(" + columnName + ")  As " + otherName + ",");}
            if (function == SQLFunction.Avg) { if (String.IsNullOrEmpty(otherName)) this._builder.Append("Avg(" + columnName + "),"); else this._builder.Append("Avg(" + columnName + ")  As " + otherName + ","); } 
            if (function == SQLFunction.Count) { if (String.IsNullOrEmpty(otherName)) this._builder.Append("Count(" + columnName + "),"); else this._builder.Append("Count(" + columnName + ")  As " + otherName + ","); }
            if (function == SQLFunction.CountBig) { if (String.IsNullOrEmpty(otherName)) this._builder.Append("Count Big(" + columnName + "),"); else this._builder.Append("Count Big(" + columnName + ")  As " + otherName + ","); } 
            if (function == SQLFunction.Max) { if (String.IsNullOrEmpty(otherName)) this._builder.Append("Max(" + columnName + "),"); else this._builder.Append("Max(" + columnName + ")  As " + otherName + ","); } 
            if (function == SQLFunction.Min) { if (String.IsNullOrEmpty(otherName)) this._builder.Append("Min(" + columnName + "),"); else this._builder.Append("Min(" + columnName + ")  As " + otherName + ","); }
            return this;
        }

        public SQLStringBuilder Table(string tableName)
        {
            return this.Table(tableName, String.Empty);
        }
        public SQLStringBuilder Table(string tableName, string otherName)
        {
            if (_type == SQLStringType.Select)
            {
                if (String.IsNullOrEmpty(tableName)) throw new ArgumentNullException("tableName");
                this._builder.Remove(this._builder.Length - 1, 1).Append(" From " + tableName);
                if (!String.IsNullOrEmpty(otherName)) this._builder.Append(" As " + otherName);
            }
            else
                this._builder.Append(" " + tableName);
            return this;
        }

        public SQLStringBuilder Join(string tableName, string otherName, JoinType type, string condition)
        {
            if (String.IsNullOrEmpty(tableName)) throw new ArgumentNullException("tableName");

            if (type == JoinType.InnerJoin) this._builder.Append(" Inner Join " + tableName + ") As " + otherName + " On " + condition);
            if (type == JoinType.LeftJoin) this._builder.Append(" Left Join " + tableName + ") As " + otherName + " On " + condition);
            if (type == JoinType.RightJoin) this._builder.Append(" Right Join " + tableName + ") As " + otherName + " On " + condition);

            return this;
        }
        public SQLStringBuilder Join(SQLStringBuilder tableName, string otherName, JoinType type, string condition)
        {
            if (tableName == null) throw new ArgumentNullException("tableName");
            if (String.IsNullOrEmpty(tableName.ToString())) throw new ArgumentNullException("tableName");

            if (type == JoinType.InnerJoin) this._builder.Append(" Inner Join (" + tableName.ToString() + ") As " + otherName + " On " + condition);
            if (type == JoinType.LeftJoin) this._builder.Append(" Left Join (" + tableName.ToString() + ") As " + otherName + " On " + condition);
            if (type == JoinType.RightJoin) this._builder.Append(" Right Join (" + tableName.ToString() + ") As " + otherName + " On " + condition);

            return this;
        }

        public SQLStringBuilder Group(params string[] columns)
        {
            if (columns.Length == 0) throw new ArgumentNullException("columns");
            this._builder.Append(" Group By ");
            foreach (string column in columns)
                this._builder.Append(column + ",");
            this._builder.Remove(this._builder.Length - 1, 1);
            return this;
        }
        public SQLStringBuilder Order(OrderType type, params string[] columns)
        {
            if (columns.Length == 0) throw new ArgumentNullException("columns");
            this._builder.Append(" Order By ");
            foreach (string column in columns)
                this._builder.Append(column + ",");
            this._builder.Remove(this._builder.Length - 1, 1);
            if (type == OrderType.ASC)
                this._builder.Append(" Asc ");
            else
                this._builder.Append(" Desc ");
            return this;
        }
        public SQLStringBuilder Order(params string[] columns)
        {
            return this.Order(OrderType.ASC, columns);
        }

        public SQLStringBuilder Where(string columnName, string condition1, string condition2, Operational opt, ConditionType type)
        {
            if (String.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");
            if (String.IsNullOrEmpty(condition1)) throw new ArgumentNullException("condition1");
            if (!this._builder.ToString().Contains("Where")) this._builder.Append(" Where ");
            if (opt == Operational.BETWEEN)
            {
                if (String.IsNullOrEmpty(condition2)) throw new ArgumentNullException("condition2");
                this._builder.Append("(" + columnName + " Between '" + condition1 + "' And '" + condition2 + "')");
            }

            if (opt == Operational.IN) this._builder.Append("(" + columnName + " In(" + condition1  + "))");
            if (opt == Operational.EQUAL) this._builder.Append("(" + columnName + " = '" + condition1 + "')");
            if (opt == Operational.NOEQUAL) this._builder.Append("(" + columnName + " != '" + condition1 + "')");
            if (opt == Operational.BIGTHEN) this._builder.Append("(" + columnName + " > '" + condition1 + "')");
            if (opt == Operational.LESSTHEN) this._builder.Append("(" + columnName + " < '" + condition1 + "')");
            if (opt == Operational.BIGEQUALTHEN) this._builder.Append("(" + columnName + " >= '" + condition1 + "')");
            if (opt == Operational.LESSEQUALTHEN) this._builder.Append("(" + columnName + " <= '" + condition1 + "')");

            if (type == ConditionType.NONE) return this;

            if (type == ConditionType.AND) this._builder.Append(" And");
            if (type == ConditionType.OR) this._builder.Append(" Or");
            return this;
        }
        public SQLStringBuilder Where(string columnName, string condition, Operational opt, ConditionType cndtype)
        {
            return this.Where(columnName, condition, String.Empty, opt, cndtype);
        }
        public SQLStringBuilder Where(string columnName, string condition, ConditionType cndtype)
        {
            return this.Where(columnName, condition, Operational.EQUAL, cndtype);
        }
        public SQLStringBuilder Where(string columnName, string condition1, string condition2, ConditionType cndtype)
        {
            return this.Where(columnName, condition1, condition2, Operational.BETWEEN, cndtype);
        }

        public SQLStringBuilder UpdateColumn(string columnName, string value)
        {
            if (String.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");
            if (String.IsNullOrEmpty(value)) throw new ArgumentNullException("value");

            this._builder.Append(columnName + " = " + value + ", ");
            return this;
        }
        public SQLStringBuilder UpdateColumn(string columnName, SQLStringBuilder value)
        {
            if (String.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");
            if (value == null) throw new ArgumentNullException("value");
            if (String.IsNullOrEmpty(value.ToString())) throw new ArgumentNullException("value");
            this._builder.Append(columnName + " = " + value.ToString() + ", ");
            return this;
        }

        public SQLStringBuilder InsertColumn(params string[] columns)
        {
            return InsertColumn(ValueType.Direct, columns);
        }
        public SQLStringBuilder InsertColumn(ValueType type, params string[] columns)
        {
            if (columns.Length == 0) throw new ArgumentNullException("columns");
            this._builder.Append("(");
            foreach (string column in columns)
                this._builder.Append(column + ",");
            this._builder.Remove(this._builder.Length - 1, 1);
            this._builder.Append(")");

            if (type == ValueType.Direct) return this;

            this._builder.Append(" Values (");
            for(int i=0;i<columns.Length;i++)
            {
                if (type == ValueType.Format)
                    this._builder.Append("'{" + i + "}',");
                if (type == ValueType.Parameter)
                    this._builder.Append("@" + columns[i].ToLower() + ",");
            }
            this._builder.Remove(this._builder.Length - 1, 1);
            this._builder.Append(")");
            return this;
        }

        public SQLStringBuilder InsertValue(params string[] values)
        {
            if (values.Length == 0) throw new ArgumentNullException("values");
            if (this._builder.ToString().Contains("Values")) throw new ArgumentException("语句中只能有一个Values关键字");

            this._builder.Append("(");
            foreach (string value in values)
                this._builder.Append("'" + values + "',");
            this._builder.Remove(this._builder.Length - 1, 1);
            this._builder.Append(")");
            return this;
        }
        public SQLStringBuilder InsertValue(SQLStringBuilder values)
        {
            if (values == null) throw new ArgumentNullException("values");
            if (String.IsNullOrEmpty(values.ToString())) throw new ArgumentNullException("values");

            this._builder.Append(values.ToString());
            return this;
        }

        /// <summary>
        /// 得到生成的SQL语句
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            string sqlString = this._builder.ToString();
            sqlString = sqlString.TrimEnd('A', 'n', 'd');
            sqlString = sqlString.TrimEnd('O', 'r');
            return sqlString;
        }
    }
}
